import { db } from "src/config/database";
import { add, modify, remove, queryOne } from "./base";

const sTableName = "snapshot_info";
const pTableName = "project_info";

function query(fields: string[], params: any) {
  const {
    project_id,
    snapshot_description,
    start_time,
    end_time,
    page_index,
    page_size,
  } = params;
  const column_st = fields.join(",");
  const values = [project_id];
  const offset = (page_index - 1) * page_size;

  // 主查询语句（添加总记录数子查询）
  let statement = `
    SELECT 
      ${column_st},
      (SELECT COUNT(*) 
       FROM ${sTableName} s2 
       JOIN ${pTableName} p2 ON s2.project_id = p2.id 
       WHERE s2.del_flag = 1 AND s2.project_id = ?
       ${snapshot_description ? " AND s2.snapshot_description LIKE ?" : ""}
       ${start_time && end_time ? " AND s2.build_time BETWEEN ? AND ?" : ""}
       ${start_time && !end_time ? " AND s2.build_time >= ?" : ""}
       ${!start_time && end_time ? " AND s2.build_time <= ?" : ""}
      ) AS total_count
    FROM ${sTableName} s 
    JOIN ${pTableName} p ON s.project_id = p.id 
    WHERE s.del_flag = 1 AND s.project_id = ?
  `;

  // 处理快照描述
  if (snapshot_description) {
    statement += " AND s.snapshot_description LIKE ?";
    values.push(`%${snapshot_description}%`);
  }

  // 处理时间范围查询
  if (start_time && end_time) {
    statement += " AND s.build_time BETWEEN ? AND ?";
    values.push(start_time, end_time);
  } else if (start_time) {
    statement += " AND s.build_time >= ?";
    values.push(start_time);
  } else if (end_time) {
    statement += " AND s.build_time <= ?";
    values.push(end_time);
  }

  // 添加倒序排序
  statement += " ORDER BY s.id DESC";

  // 构建分页语句
  statement += " LIMIT ? OFFSET ?";
  values.push(page_size, offset);

  // 复制条件参数用于子查询（不含分页参数）
  const subqueryValues = [...values.slice(0, -2)];

  return new Promise((resolve, reject) => {
    db!.all(statement, [...subqueryValues, ...values], (err, rows: any[]) => {
      if (err) {
        reject(err);
      } else {
        // 从第一条记录中获取总记录数（所有记录的total_count相同）
        const total = rows.length > 0 ? rows[0].total_count : 0;

        // 返回分页数据和总记录数
        resolve({
          list: rows,
          total,
        });
      }
    });
  });
}

function batchModify(tableName: string, updateParams: any, whereParams: any) {
  // 处理要更新的字段和值
  const updateColumns = Object.keys(updateParams);
  const updateValues = Object.values(updateParams);

  // 生成SET子句：column1 = ?, column2 = ?
  const setClause = updateColumns.map((key) => `${key} = ?`).join(",");

  // 处理WHERE条件
  const whereColumns = Object.keys(whereParams);
  const whereValues = Object.values(whereParams);

  // 生成WHERE子句：column1 = ? AND column2 = ?
  const whereClause = whereColumns.map((key) => `${key} = ?`).join(" AND ");

  // 组合SQL语句
  const statement = `UPDATE ${tableName} SET ${setClause} WHERE ${whereClause}`;

  // 合并参数值（先更新值，后条件值）
  const values = [...updateValues, ...whereValues];

  return new Promise((resolve, reject) => {
    db!.run(statement, values, function (err) {
      err ? reject(err) : resolve(this);
    });
  });
}

export default {
  add: add.bind(null, sTableName),
  modify: modify.bind(null, sTableName),
  batchModify: batchModify.bind(null, sTableName),
  remove: remove.bind(null, sTableName),
  queryOne: queryOne.bind(null, sTableName),
  query: query,
};
